﻿//功能描述：合同管理数据访问层
using Dapper;
using Hong.Common.Encryption;
using Hong.IRepository;
using Hong.Model.Dto.PesponseDto.ResponseModel.ResultModel;
using Hong.Model.SysDomain.Model_ContractManagement;
using Hong.Model.SysDomain.Model_ContractManagement.StaModel;
using Hong.Repository.Data;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace Hong.Repository.Repository.Repository_ContractManagement
{
    public class ContractRepository: IContractRepository
    {
        #region 构造器注入
        private readonly T_HongShi_3Context _t_HongShi_3Context;
        private readonly DataBase _dataBase;
        private readonly DapperHelper _dapperHelper;
        private readonly ILogger<ContractRepository> _logger;
        private readonly IDistributedCache _cache;
        //注入解密SQL连接字符串类
        private readonly SecurityStatic _securityStatic;
        public ContractRepository(T_HongShi_3Context t_HongShi_3Context, DataBase dataBase, ILogger<ContractRepository> logger, DapperHelper dapperHelper, IDistributedCache cache, SecurityStatic securityStatic)
        {
            _t_HongShi_3Context = t_HongShi_3Context;
            _dataBase = dataBase;
            _logger = logger;
            _logger.LogDebug(1, "NLog injected into HomeController");
            _dapperHelper = dapperHelper;
            _cache = cache;
            _securityStatic = securityStatic;
        }
        #endregion


        #region 合同录入

        #region 查询客户
        /// <summary>
        /// 查询客户
        /// </summary>
        /// <param name="name"></param>
        /// <param name="edCountryId2"></param>
        /// <param name="edCountryId3"></param>
        /// <param name="edTypeId"></param>
        /// <returns></returns>
        public List<ViewCustomer> GetCustomers(string name, string edCountry2Nmae, string edCountry3Nmae, string edTypeName)
        {
            var q = from a in _t_HongShi_3Context.Customers
                    join b in _t_HongShi_3Context.Extendeds on a.CustId equals b.CustomerId
                    join c in _t_HongShi_3Context.Addresses on b.EdCountryId2 equals c.AddressId
                    join d in _t_HongShi_3Context.Addresses on b.EdCountryId3 equals d.AddressId
                    join e in _t_HongShi_3Context.Dictionaries on Convert.ToInt32(b.EdTypeId) equals e.DicId
                    select new ViewCustomer
                    {
                        CustomerId = b.CustomerId,
                        CustAbbreviation = a.CustAbbreviation,
                        CustAddress = a.CustAddress,
                        CustEmail = a.CustEmail,
                        CustFaxNumber = a.CustFaxNumber,
                        CustId = a.CustId,
                        CustName = a.CustName,
                        CustPhone = a.CustPhone,
                        CustPhones = a.CustPhones,
                        EdBankAccount = b.EdBankAccount,
                        EdBankName = b.EdBankName,
                        EdBeiZhu = b.EdBeiZhu,
                        EdBuMen = b.EdBuMen,
                        EdCountryId1 = b.EdCountryId1,
                        EdCountryId2 = b.EdCountryId2,
                        EdCountryId3 = b.EdCountryId3,
                        EdCreateTime = b.EdCreateTime,
                        EdEmail = b.EdEmail,
                        EdEnterprise = b.EdEnterprise,
                        EdId = b.EdId,
                        EdIndustryId = b.EdIndustryId,
                        EdIsheZuo = b.EdIsheZuo,
                        EdName = b.EdName,
                        EdTypeId =Convert.ToInt32(b.EdTypeId),
                        EdCountryId2Name = c.AddressName,
                        EdCountryId3Name = d.AddressName,
                        EdTypeName=e.DicName
                    };
            if (name!=null)
            {
                q = q.Where(m => m.CustName.Contains(name));
            }
            if (edCountry2Nmae != null)
            {
                q = q.Where(m => m.EdCountryId2Name == edCountry2Nmae);
            }
            if (edCountry3Nmae != null)
            {
                q = q.Where(m => m.EdCountryId3Name == edCountry3Nmae);
            }
            if (edTypeName != null)
            {
                q = q.Where(m => m.EdTypeName == edTypeName);
            }
            _logger.LogInformation("客户查询成功");
            return q.ToList();
        }
        #endregion

            #region 结构形式

                #region 结构形式下拉框
        /// <summary>
        /// 结构形式下拉框
        /// </summary>
        /// <returns></returns>
        public List<StaStructuralstyleInfo> GetStaStru()
        {
            var list = _t_HongShi_3Context.StructuralstyleInfos.ToList();
            var q = GetStruChildren(list, 0);
            return q;
        }
        #endregion

                #region 递归结构形式
        /// <summary>
        /// 递归结构形式
        /// </summary>
        /// <param name="list"></param>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<StaStructuralstyleInfo> GetStruChildren(List<StructuralstyleInfo> list, int pid)
        {
            return list.Where(m => m.PId == pid).Select(u => new StaStructuralstyleInfo
            {
                Structuralstyle = u.Structuralstyle,
                StructuralstyleId = u.StructuralstyleId,
                PId = u.PId,
                children = GetStruChildren(list, u.StructuralstyleId).Count == 0 ? null : GetStruChildren(list, u.StructuralstyleId)
            }).ToList();
        }
        #endregion

        #endregion

            #region 建筑分类

                #region 建筑分类下拉框
        /// <summary>
        /// 建筑分类下拉框
        /// </summary>
        /// <returns></returns>
        public List<StaBuildingTypeInfo> GetBuild()
        {
            var list = _t_HongShi_3Context.BuildingTypeInfos.ToList();
            var q = GetBuildingType(list, 0);
            return q;
        }
        #endregion

                #region 递归建筑分类
        /// <summary>
        /// 递归建筑分类
        /// </summary>
        /// <param name="list"></param>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<StaBuildingTypeInfo> GetBuildingType(List<BuildingTypeInfo> list, int pid)
        {
            return list.Where(m => m.Pid == pid).Select(u => new StaBuildingTypeInfo
            {
                BuildingType = u.BuildingType,
                BuildingTypeName = u.BuildingTypeName,
                Pid = u.Pid,
                children = GetBuildingType(list, u.BuildingType).Count == 0 ? null : GetBuildingType(list, u.BuildingType)
            }).ToList();
        }
        #endregion

        #endregion

            #region 添加合同
        /// <summary>
        /// 添加合同
        /// </summary>
        /// <param name="contractInfo"></param>
        /// <returns></returns>
        public int AddContract(ContractInfo contractInfo)
        {
            //事务
            using (var scope = _t_HongShi_3Context.Database.BeginTransaction())
            {
                try
                {
                    var contractId = Guid.NewGuid().ToString();
                    contractInfo.ContractId = contractId;
                    _t_HongShi_3Context.ContractInfos.Add(contractInfo);
                    _t_HongShi_3Context.SaveChanges();
                    ContractrateInfo contractrateInfo = new ContractrateInfo();
                    var contractrateId = Guid.NewGuid().ToString();
                    contractrateInfo.ContractrateId = contractrateId;
                    contractrateInfo.ContractId = contractId;
                    contractrateInfo.RealyProceeds = 0;
                    contractrateInfo.Remark = "签订合同,未收费";
                    contractrateInfo.ProceedsTime = contractInfo.CreateTime;
                    _t_HongShi_3Context.ContractrateInfos.Add(contractrateInfo);
                    _t_HongShi_3Context.SaveChanges();
                    //事务提交
                    scope.Commit();
                    //返回
                    return 1;
                }
                catch (Exception ex)
                {
                    //事务回滚
                    scope.Rollback();
                    _logger.LogError(ex.ToString());
                    throw;
                }
            }
                
        }
        #endregion

        #endregion

        #region 合同列表

            #region 合同查询页
        /// <summary>
        /// 合同查询页
        /// </summary>
        /// <param name="pageindex">当前页</param>
        /// <param name="pagesize">页大小</param>
        /// <param name="contractShowCondition">条件类</param>
        /// <returns></returns>
        public async Task<PageResponse<ViewContractInfo>> GetContractInfos(int pageindex, int pagesize, ContractShowCondition contractShowCondition)
        {
            var sql = " exec p_ShowContractInfo @contractName,@contractNumber,@contractType,@development,@projectDirector,@ownerDirector,@minoriginalContractPrice,@maxoriginalContractPrice,@minrealyContractPrice,@maxrealyContractPrice,@ownerType,@minbuildingArea,@maxbuildingArea,@mindrawingNum,@maxdrawingNum,@mingetDrawingNum,@maxgetDrawingNum,@subsidiary,@begincreateTime,@endcreateTime,@minfloorNum,@maxfloorNum,@mingetFloorNum,@maxgetFloorNum,@projectPlace ";
            try
            {

                var list = await _dapperHelper.ExecuteProAsync<ViewContractInfo>(sql, new
                {
                    contractName = contractShowCondition.contractName,
                    contractNumber = contractShowCondition.contractNumber,
                    contractType = contractShowCondition.contractType,
                    development = contractShowCondition.development,
                    projectDirector = contractShowCondition.projectDirector,
                    ownerDirector = contractShowCondition.ownerDirector,
                    minoriginalContractPrice = contractShowCondition.minoriginalContractPrice,
                    maxoriginalContractPrice = contractShowCondition.maxoriginalContractPrice,
                    minrealyContractPrice = contractShowCondition.minrealyContractPrice,
                    maxrealyContractPrice = contractShowCondition.maxrealyContractPrice,
                    ownerType = contractShowCondition.ownerType,
                    minbuildingArea = contractShowCondition.minbuildingArea,
                    maxbuildingArea = contractShowCondition.maxbuildingArea,
                    mindrawingNum = contractShowCondition.mindrawingNum,
                    maxdrawingNum = contractShowCondition.maxdrawingNum,
                    mingetDrawingNum = contractShowCondition.mingetDrawingNum,
                    maxgetDrawingNum = contractShowCondition.maxgetDrawingNum,
                    subsidiary = contractShowCondition.subsidiary,
                    begincreateTime = contractShowCondition.begincreateTime,
                    endcreateTime = contractShowCondition.endcreateTime,
                    minfloorNum = contractShowCondition.minfloorNum,
                    maxfloorNum = contractShowCondition.maxfloorNum,
                    mingetFloorNum = contractShowCondition.mingetFloorNum,
                    maxgetFloorNum = contractShowCondition.maxgetFloorNum,
                    projectPlace = contractShowCondition.projectPlace
                });
                var rlist = list.OrderBy(m => m.contractNumber).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();
                PageResponse<ViewContractInfo> q = new PageResponse<ViewContractInfo>(rlist, pageindex, list.Count());
                return q;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.ToString());
                throw;
            }
        }
        #endregion

            #region 合同反填
        public ContractInfo GetContractInfosById(string id)
        {
            var item = _t_HongShi_3Context.ContractInfos.FirstOrDefault(m => m.ContractId == id);
            return item;
        }
        #endregion

            #region 合同修改
        /// <summary>
        /// 合同修改
        /// </summary>
        /// <param name="contractInfo"></param>
        /// <returns></returns>
        public int UpContractInfos(ContractInfo contractInfo)
        {
            try
            {
                _t_HongShi_3Context.Entry(contractInfo).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                _logger.LogInformation("合同修改成功");
                return _t_HongShi_3Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogInformation(ex.ToString());
                throw;
            }
        }

        #endregion

            #region 合同删除
        /// <summary>
        /// 合同删除
        /// </summary>
        /// <param name="contractInfo"></param>
        /// <returns></returns>
        public int DelContractInfos(string id)
        {
            try
            {
                var item = _t_HongShi_3Context.ContractInfos.Where(m => m.ContractId == id).FirstOrDefault();
                _t_HongShi_3Context.ContractInfos.Remove(item);
                _logger.LogInformation("合同删除成功");
                return _t_HongShi_3Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogInformation(ex.ToString());
                throw;
            }
        }

        #endregion

        #region 合同收费列表
        /// <summary>
        /// 合同收费列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ViewContracratetInfo> GetContractLists(string id)
        {
            using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                var q = conn.Query<ViewContracratetInfo>($" select b.realyContractPrice,b.contractName,a.contractId,a.contractrateId,a.payeeName,a.proceedsTime,a.realyProceeds,a.remark,b.projectDirector, (a.realyProceeds/b.realyContractPrice*100) as adm from ContractrateInfo a join ContractInfo b on a.contractId=b.contractId where b.contractId ='{id}' ").ToList();
                return q;
            }
        }
        #endregion

        #region 合同收费
        /// <summary>
        /// 合同收费
        /// </summary>
        /// <param name="contractrateInfo"></param>
        /// <returns></returns>
        public int AddContractrateInfo(ContractrateInfo contractrateInfo)
        {
            try
            {
                var guid = Guid.NewGuid().ToString();
                contractrateInfo.ContractrateId = guid;
                _t_HongShi_3Context.ContractrateInfos.Add(contractrateInfo);
                return _t_HongShi_3Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogInformation(ex.ToString());
                throw;
            }
        }

        #endregion

        #region 收费修改
        /// <summary>
        /// 收费修改
        /// </summary>
        /// <param name="contractrateInfo"></param>
        /// <returns></returns>
        public int UpContractrateInfo(ContractrateInfo contractrateInfo)
        {
            try
            {
                _t_HongShi_3Context.Entry(contractrateInfo).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                _logger.LogInformation("收费修改成功");
                return _t_HongShi_3Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogInformation(ex.ToString());
                throw;
            }
        }

        #endregion

        #region 收费删除
        /// <summary>
        /// 收费删除
        /// </summary>
        /// <param name="contractInfo"></param>
        /// <returns></returns>
        public int DelContractrateInfo(string id)
        {
            try
            {
                var item = _t_HongShi_3Context.ContractrateInfos.Where(m => m.ContractrateId == id).FirstOrDefault();
                _t_HongShi_3Context.ContractrateInfos.Remove(item);
                _logger.LogInformation("收费删除成功");
                return _t_HongShi_3Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogInformation(ex.ToString());
                throw;
            }
        }
        #endregion

        #region 收费统计

            #region 总额

            #endregion
            #region 月份
        public List<Showmounth> Getmounth()
        {

            List<Showmounth> list = new List<Showmounth>();
            var showMounth = _cache.GetString("showMounth");
            if (string.IsNullOrWhiteSpace(showMounth))
            {

                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    list = conn.Query<Showmounth>("exec p_showmounth").ToList();
                    _cache.SetString("showMounth", JsonConvert.SerializeObject(list));
                    return list;
                }
            }
            else
            {
                list = JsonConvert.DeserializeObject<List<Showmounth>>(showMounth);
                return list;
            }
        }
        #endregion
            #region 季度
        public List<Showquarter> Getquarter()
        {
            List<Showquarter> list = new List<Showquarter>();
            var showQuarter = _cache.GetString("showQuarter");
            if (string.IsNullOrWhiteSpace(showQuarter))
            {
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    list = conn.Query<Showquarter>("exec p_showquarter").ToList();
                    _cache.SetString("showQuarter", JsonConvert.SerializeObject(list));
                    return list;
                }
            }
            else
            {
                list = JsonConvert.DeserializeObject<List<Showquarter>>(showQuarter);
                return list;
            }
        }
        #endregion
            #region 合同类型
        public List<ShowcontractType> GetcontractType()
        {
            List<ShowcontractType> list = new List<ShowcontractType>();
            var showContracType = _cache.GetString("showContracType");
            if (string.IsNullOrWhiteSpace(showContracType))
            {
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    list = conn.Query<ShowcontractType>("exec p_showcontractType").ToList();
                    _cache.SetString("showContracType", JsonConvert.SerializeObject(list));
                    return list;
                }
            }
            else
            {
                list = JsonConvert.DeserializeObject<List<ShowcontractType>>(showContracType);
                return list;
            }
        }
        #endregion

        #endregion

        #endregion






    }
}
